package com.cplatform.movie.back.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;

/**
 * 读取excel add by chensl
 */
public class ReadExcel {

	private static final Logger log = Logger.getLogger(ReadExcel.class);
	private static Workbook wb = null;
	private static Cell cell = null;
	private static Row row = null;
	public String inputStream2String(InputStream in) throws IOException {
		StringBuffer out = new StringBuffer();
		byte[] b = new byte[1024];
		for (int n; (n = in.read(b)) != -1;) {
			out.append(new String(b, 0, n));
		}
		return out.toString();
	}
	
	/**
	 * 读取excel文件内容
	 * 
	 * @param filePath
	 * @throws FileNotFoundException
	 * @throws FileFormatException
	 */
	public static List<HashMap<Integer, String[]>> readExcel(InputStream stream, String fileType, String goods)
			throws FileNotFoundException, FileFormatException {
		List<HashMap<Integer, String[]>> goodList= new ArrayList<HashMap<Integer,String[]>>();
		String[]str=null;
		HashMap<Integer, String[]> goodMap=new HashMap<Integer, String[]>();
		// 获取workbook对象
		Workbook workbook = null;
		try {
			if (fileType.equals("xls")) {
				workbook = new HSSFWorkbook(stream);
			} else if (fileType.equals("xlsx")) {
				workbook = new XSSFWorkbook(stream);
			}
			// 读文件 一个sheet一个sheet地读取 workbook.getNumberOfSheets(),默认读取第一个。
			for (int numSheet = 0; numSheet < 1; numSheet++) {
				Sheet sheet = workbook.getSheetAt(numSheet);
				if (sheet == null) {
					continue;
				}
				int count_row = sheet.getLastRowNum() + 1;
				row = sheet.getRow(0);
				if(row==null){
					return null;
				}
				int count_line = row.getPhysicalNumberOfCells();
				for (int i = 0; i < count_row; i++) {
					goodMap=new HashMap<Integer, String[]>();
					str = new String[count_line];
					for (int j = 0; j < count_line; j++) {
						row = sheet.getRow(i);
						cell = row.getCell(j);
						if (i==0) {
							str[j] = getCellValue(cell,true);
						}else{
							str[j] = getCellValue(cell,true);
						}
					}
					goodMap.put(i, str);
					goodList.add(goodMap);
				}

				cell = null;
				row = null;
				sheet = null;
				wb = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (workbook != null) {
				try {
					 ((InputStream) workbook).close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return goodList;
	}
	
	/**
	 * 取单元格的值
	 * 
	 * @param cell
	 *            单元格对象
	 * @param treatAsStr
	 *            为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
	 * @return
	 */
	private static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return "";
		}
		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
	
	
	/**
	 * 去掉字符串右边的空格
	 * 
	 * @param str
	 *            要处理的字符串
	 * @return 处理后的字符串
	 */
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}

	/**
	 * 读取excel 参数说明 （in 所要读取的文件流 ignoreRows 从excel的第几行开始读取 filePath 上传文件的路径）
	 */
	public static List<String[]> getData2(InputStream in, int ignoreRows, String filePath) {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(in);
			Cell cell = null;
			// 获取sheet1表格
			Sheet st = wb.getSheetAt(0);
			// 第一行、最后一行为标题、说明，不读取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum() - 1; rowIndex++) {
				Row row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				// 读取每一列的值
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = new DecimalFormat("0.##").format(cell.getNumericCellValue());
								break;
							default:
								value = "";
						}
					}
					values[columnIndex] = rightTrim(value);
				}
				result.add(values);
			}
		}
		catch (Exception ex) {
			log.error("读取excel" + ex.getMessage());
		}
		finally {
			try {
				in.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 读取excell，只支持读取一个sheet
	 * 
	 * @param in
	 *            上传文件流
	 * @param ignoreRows
	 *            设置读取初始行，0表示从第一行开始读取，1表示从第二行开始读取
	 * @param endRow
	 *            设置读取末行，0表示读取到最后一行，1表示读取到倒数第二行
	 * @return
	 */
	public static List<String[]> getData(InputStream in, int ignoreRows, int endRow) {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(in);
			Cell cell = null;
			// 获取sheet1表格
			Sheet st = wb.getSheetAt(0);
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum() - endRow; rowIndex++) {
				Row row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				// 读取每一列的值
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = new DecimalFormat("0.##").format(cell.getNumericCellValue());
								break;
							default:
								value = "";
						}
					}
					values[columnIndex] = rightTrim(value);
				}
				result.add(values);
			}
		}
		catch (Exception ex) {
			log.error("读取excel" + ex.getMessage());
		}
		finally {
			try {
				in.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 解析第三方码excell
	 * 
	 * @param in
	 * @param ignoreRows
	 * @param filePath
	 * @return
	 */
	public static String[] getThirdCodeData(InputStream in, int ignoreRows, String filePath) {
		String[] result = null;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(in);
			Cell cell = null;
			// 获取sheet1表格
			Sheet st = wb.getSheetAt(0);
			result = new String[st.getLastRowNum() + 1];
			Arrays.fill(result, "");
			// 从第一行读取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				Row row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				// 读取每一列的值
				for (short columnIndex = 0; columnIndex < 1; columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = new DecimalFormat("0.##").format(cell.getNumericCellValue());
								break;
							default:
								value = "";
						}
					}
					result[rowIndex] = rightTrim(value);
				}
			}
		}
		catch (Exception ex) {
			log.error("读取excel" + ex.getMessage());
		}
		finally {
			try {
				in.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 读取excel 参数说明 （in 所要读取的文件流 ignoreRows 从excel的第几行开始读取 filePath 上传文件的路径）
	 */
	public static String[] getCardData(InputStream in, int ignoreRows, String filePath) {
		String[] result = null;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(in);
			Cell cell = null;
			// 获取sheet1表格
			Sheet st = wb.getSheetAt(0);
			result = new String[st.getLastRowNum()];
			Arrays.fill(result, "");
			// 第一行为标题，不读取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				Row row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				// 读取每一列的值
				for (short columnIndex = 0; columnIndex < 1; columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = new DecimalFormat("0.##").format(cell.getNumericCellValue());
								break;
							default:
								value = "";
						}
					}
					result[rowIndex - 1] = rightTrim(value);
				}
			}
		}
		catch (Exception ex) {
			log.error("读取excel" + ex.getMessage());
		}
		finally {
			try {
				in.close();
			}
			catch (IOException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
}
